﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.IO;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Diagnostics;
using System.Threading;

namespace DBTools
{
    public partial class Main : Form
    {
        private readonly string _iniPath = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + "\\modelgenerator.ini";
        private string _chk_database = string.Empty;
        private string[] _dbCbox_Items;
        private string[] _tabCbox_Items;

        public Main()
        {
            InitializeComponent();

            btn_conn.Click += Btn_conn_Click;
            //cbox_database.SelectedIndexChanged += Cbox_database_SelectedIndexChanged;
            cbox_database.Enter += Cbox_TextChanged_Fuzzy_Enter;
            cbox_tabls.Enter += Cbox_TextChanged_Fuzzy_Enter;

            cbox_database.TextUpdate += Cbox_TextChanged_Fuzzy_Search;
            cbox_tabls.TextUpdate += Cbox_TextChanged_Fuzzy_Search;

            cbox_database.LostFocus += Cbox_database_LostFocus;
            cbox_tabls.LostFocus += Cbox_tabls_LostFocus;

            btn_checkdir.Click += Btn_checkdir_Click;
            btn_cthis.Click += Btn_cthis_Click;
            btn_call.Click += Btn_call_Click;

            StringBuilder sbdr = new StringBuilder(150);
            GetPrivateProfileString("setting", "host", "", sbdr, 1024, _iniPath);
            if (sbdr.Length > 0) txt_host.Text = sbdr.ToString();

            GetPrivateProfileString("setting", "user", "", sbdr, 1024, _iniPath);
            if (sbdr.Length > 0) txt_user.Text = sbdr.ToString();

            GetPrivateProfileString("setting", "passwd", "", sbdr, 1024, _iniPath);
            if (sbdr.Length > 0) txt_passwd.Text = sbdr.ToString();

            GetPrivateProfileString("setting", "dir", "", sbdr, 1024, _iniPath);
            if (sbdr.Length > 0) cbox_savepath.DataSource = sbdr.ToString().Split(';').ToList();

            GetPrivateProfileString("setting", "namespace", "", sbdr, 1024, _iniPath);
            if (sbdr.Length > 0) cbox_namespace.DataSource = sbdr.ToString().Split(';').ToList();

            GetPrivateProfileString("setting", "database", "", sbdr, 1024, _iniPath);
            if (sbdr.Length > 0) _chk_database = sbdr.ToString();

            CheckForIllegalCrossThreadCalls = false;
            if (!string.IsNullOrWhiteSpace(txt_host.Text) && !string.IsNullOrWhiteSpace(txt_user.Text) && !string.IsNullOrWhiteSpace(txt_passwd.Text))
                Task.Run(() => InitDataBase());
        }

        private void Cbox_tabls_LostFocus(object sender, EventArgs e)
        {
            var cbox = (ComboBox)sender;
            cbox.DroppedDown = false;
        }

        private void Cbox_database_LostFocus(object sender, EventArgs e)
        {
            var cbox = (ComboBox)sender;
            cbox.DroppedDown = false;

            string dbName = cbox.SelectedItem?.ToString();
            if (string.IsNullOrWhiteSpace(dbName))
                return;

            DB.SetDataBase(dbName);

            _tabCbox_Items = GetTableNameList().ToArray();
            cbox_tabls.Items.Clear();
            cbox_tabls.Items.AddRange(_tabCbox_Items);
        }

        private void Cbox_TextChanged_Fuzzy_Search(object sender, EventArgs e)
        {
            var cbox = (ComboBox)sender;

            var val = cbox.Text;

            var dataSource = GetCboxDataSource(cbox.Name);

            if (dataSource == null)
            {
                return;
            }

            if (string.IsNullOrWhiteSpace(val))
            {
                cbox.Items.AddRange(dataSource);
                return;
            }

            var dataArr = dataSource.Where(a => a.IndexOf(val, StringComparison.OrdinalIgnoreCase) > -1).ToList();
            if (cbox.Items.Count < 1)
            {
                dataArr.Add("");
            }
            cbox.Items.Clear();
            cbox.Items.AddRange(dataArr.ToArray());
            cbox.SelectionStart = val.Length;
            Cursor = Cursors.Default;
            cbox.DroppedDown = true;
        }

        private void Cbox_TextChanged_Fuzzy_Enter(object sender, EventArgs e)
        {
            var cbox = (ComboBox)sender;
            cbox.DroppedDown = true;
        }

        private string[] GetCboxDataSource(string boxName)
        {
            return boxName == "cbox_database" ? _dbCbox_Items : _tabCbox_Items;
        }

        private void InitDataBase()
        {
            try
            {
                DB.Init(txt_host.Text, txt_user.Text, txt_passwd.Text);
                _dbCbox_Items = GetDatabaseNameList().ToArray();
                cbox_database.Items.Clear();
                cbox_database.Items.AddRange(_dbCbox_Items);
                if (!string.IsNullOrWhiteSpace(_chk_database))
                {
                    cbox_database.SelectedIndex = cbox_database.Items.IndexOf(_chk_database);
                    Cbox_database_LostFocus(cbox_database, null);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(@"连接数据库失败" + ex.Message, @"提示", MessageBoxButtons.OK);
                return;
            }
            WritePrivateProfileString("setting", "host", txt_host.Text, _iniPath);
            WritePrivateProfileString("setting", "user", txt_user.Text, _iniPath);
            WritePrivateProfileString("setting", "passwd", txt_passwd.Text, _iniPath);
        }

        private void Btn_call_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrWhiteSpace(cbox_tabls.SelectedItem?.ToString())) return;

            WritePrivateProfileString("setting", "database", cbox_database.Text, _iniPath);
            WritePrivateProfileString("setting", "namespace", cbox_namespace.Text, _iniPath);

            //RemoveNotExistsFile();
            SaveAllFile();
            Process.Start(cbox_savepath.Text);
            SaveIni();
        }

        private void Btn_cthis_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrWhiteSpace(cbox_tabls.SelectedItem?.ToString())) return;

            WritePrivateProfileString("setting", "database", cbox_database.Text, _iniPath);
            WritePrivateProfileString("setting", "namespace", cbox_namespace.Text, _iniPath);

            SaveFile(cbox_tabls.SelectedItem.ToString());
            Process.Start(cbox_savepath.Text);
            SaveIni();
        }

        private void Btn_checkdir_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog fbd = new FolderBrowserDialog();
            if (fbd.ShowDialog() != DialogResult.OK) return;

            cbox_savepath.Text = fbd.SelectedPath;
        }

        private void Btn_conn_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrWhiteSpace(txt_user.Text) || string.IsNullOrWhiteSpace(txt_passwd.Text))
                return;
            Task.Run(() => InitDataBase());
        }

        private List<string> GetDatabaseNameList()
        {
            return DB.ExecuteToList($"SELECT [name] FROM [master]..[sysdatabases] WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'northwind', 'pubs') ORDER BY [crdate] DESC;").ToList();
        }

        private List<string> GetTableNameList()
        {
            return DB.ExecuteToList($"SELECT [TABLE_NAME] FROM [INFORMATION_SCHEMA].[TABLES]");
        }

        private string GetTableComment(string table)
        {
            return DB.ExecuteScalar(@$"SELECT [b].[value]
                                       FROM [sys].[sysobjects] [a] LEFT JOIN [sys].[extended_properties] [b] ON [b].[major_id] = [a].[id]
                                       WHERE [a].[xtype] = 'U' AND [b].[minor_id] = 0 AND [b].[name] = 'MS_Description' AND [a].[name] = {table};") as string;
        }

        private void SaveFile(string tableName)
        {
            string filePath = GetFilePath(tableName);

            string tableComment = GetTableComment(tableName);
            DataTable dt = DB.ExecuteToDataTable($@"SELECT  [a].[COLUMN_NAME], [a].[DATA_TYPE], CASE [a].[IS_NULLABLE] WHEN 'YES' THEN 1 ELSE 0 END [IS_NULLABLE], CASE [a].[COLUMN_NAME] WHEN [a].[COLUMN_NAME] THEN 1 ELSE 0 END [IS_PRIMARYKEY], [a].[COLUMN_DEFAULT], d.[value] [COLUMN_COMMENT]
                                                    FROM    [INFORMATION_SCHEMA].[COLUMNS] [a]
                                                            LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] [b] ON [b].[TABLE_NAME] = [a].[TABLE_NAME]
                                                            LEFT JOIN [sys].[columns] [c] ON [c].[name] = [a].[COLUMN_NAME] AND [c].[object_id] = OBJECT_ID([a].[TABLE_NAME])
                                                            LEFT JOIN [sys].[extended_properties] [d] ON [d].[major_id] = [c].[object_id] AND  [d].[minor_id] = [c].[column_id] AND [d].[name] = 'MS_Description'
                                                    WHERE   [a].[TABLE_NAME] = {tableName} AND [a].[COLUMN_NAME] NOT IN ('VersionNumber', 'CreatedTime', 'CreatedGUID', 'CreatedName', 'ModifiedTime', 'ModifiedGUID', 'ModifiedName')                                                    
                                                    ORDER BY [a].[ORDINAL_POSITION];");
            File.WriteAllText(filePath, DrawModelClass(tableComment, tableName, dt, string.Empty), Encoding.UTF8);
        }

        private string GetFilePath(string tableName)
        {
            if (!Directory.Exists(cbox_savepath.Text))
            {
                Directory.CreateDirectory(cbox_savepath.Text);
            }
            return $"{cbox_savepath.Text}\\{ToCamelString(tableName.TrimStart(new[] { 'x', '_' }))}.cs";
        }

        private void SaveAllFile()
        {
            foreach (var item in cbox_tabls.Items)
            {
                Task.Run(() => SaveFile(item.ToString()));
            }
        }

        private void SaveIni()
        {
            List<string> savepathList = cbox_savepath.DataSource == null ? new List<string>() : (cbox_savepath.DataSource as List<string>).ToList();
            savepathList.Insert(0, cbox_savepath.Text);
            savepathList = savepathList.Distinct().Where(a => !string.IsNullOrWhiteSpace(a)).ToList();
            cbox_savepath.DataSource = savepathList;

            List<string> namespaceList = cbox_namespace.DataSource == null ? new List<string>() : (cbox_namespace.DataSource as List<string>).ToList();
            namespaceList.Insert(0, cbox_namespace.Text);
            namespaceList = namespaceList.Distinct().Where(a => !string.IsNullOrWhiteSpace(a)).ToList();
            cbox_namespace.DataSource = namespaceList;

            WritePrivateProfileString("setting", "dir", string.Join(";", savepathList).Trim(';'), _iniPath);
            WritePrivateProfileString("setting", "namespace", string.Join(";", namespaceList).Trim(';'), _iniPath);
        }

        private string DrawModelClass(string tableComment, string tableName, DataTable dt, string notMappedPropertyText)
        {
            StringBuilder sbdr = new StringBuilder(Convert.ToInt32(dt.Rows.Count * Math.Pow(10, 4)));
            sbdr.AppendLine("using System;");
            sbdr.AppendLine($"\r\n namespace {cbox_namespace.Text} \r\n {{");
            if (!string.IsNullOrWhiteSpace(tableComment))
            {
                sbdr.AppendLine("    /// <summary>");
                sbdr.AppendLine($"    /// {tableComment.Replace("\r\n", "\r\n    /// ")}");
                sbdr.AppendLine("    /// </summary>");
            }
            sbdr.AppendLine($"    [Serializable]");
            sbdr.AppendLine($"    [EntityName(\"{tableName}\")]");

            sbdr.AppendLine($"    public abstract class {ToCamelString(chk_remove_prefix.Checked ? tableName.TrimStart(new char[] { 'x', '_' }) : tableName)} : Entity\r\n    {{");

            sbdr.AppendLine(DrawColumnsModel(dt));

            sbdr.AppendLine("    }\r\n}");
            return sbdr.ToString();
        }

        private string DrawColumnsModel(DataTable dt)
        {
            StringBuilder sbdr = new StringBuilder(Convert.ToInt32(dt.Rows.Count * 2e4));

            foreach (DataRow row in dt.Rows)
            {
                string dataType = row["DATA_TYPE"].ToString();
                bool isNullable = row["IS_NULLABLE"].Equals(1);
                string columnName = row["COLUMN_NAME"].ToString();
                var defValue = row["COLUMN_DEFAULT"];
                string type = MapCsharpType(dataType, isNullable);

                if (!string.IsNullOrWhiteSpace(row["COLUMN_COMMENT"].ToString()))
                {
                    sbdr.AppendLine(GetCSharpComment(row["COLUMN_COMMENT"].ToString(), 2));
                }
                sbdr.AppendLine($"        [EntityColumn(\"{columnName}\")]");

                var csharpDefValue = DrawDefaultValue(defValue, type);

                sbdr.AppendLine($"        public virtual {type} {ToCamelString(chk_remove_prefix.Checked ? columnName.TrimStart(new[] { 'x', '_' }) : columnName)} {{ get; set; }} {(string.IsNullOrWhiteSpace(csharpDefValue) ? "" : $" = {csharpDefValue};") }");
            }
            return sbdr.ToString();
        }

        private string DrawDefaultValue(object defValue, string type)
        {
            StringComparison scpr = StringComparison.OrdinalIgnoreCase;
            if (defValue == DBNull.Value || defValue == null)
            {
                return string.Empty;
            }
            var strDefValue = defValue.ToString().TrimStart('(').TrimEnd(')');
            StringBuilder sbdr = new StringBuilder(20);
            if (",string,object,".IndexOf("," + type + ",", scpr) > -1)
            {
                sbdr.AppendFormat("\"{0}\"", strDefValue.TrimStart('\'').TrimEnd('\''));
            }
            else if (type.Equals("DateTime", scpr))
            {
                sbdr.Append(strDefValue.Equals("getdate()") ? "DateTime.Now" : "");
            }
            else if (",int,decimal,long,float,short,,,".IndexOf("," + type + ",", scpr) > -1)
            {
                sbdr.Append(strDefValue.TrimStart('(').TrimEnd(')'));
            }
            else if (type.Equals("Guid", scpr))
            {
                sbdr.Append("Guid.NewGuid()");
            }
            else if (type.Equals("bool", scpr) || type.Equals("bool?", scpr))
            {
                sbdr.Append(strDefValue.Equals("b'(1)'").ToString().ToLower());
            }
            else
            {
                sbdr.Append(defValue);
            }

            return sbdr.ToString();
        }

        private string MapCsharpType(string dbtype, bool isNullable)
        {
            if (string.IsNullOrEmpty(dbtype)) return dbtype;
            string csharpType;
            switch (dbtype.ToLower())
            {
                case "int":
                    csharpType = "int";
                    break;
                case "text":
                case "char":
                case "nchar":
                case "ntext":
                case "varchar":
                case "nvarchar":
                    csharpType = "string";
                    break;
                case "bigint":
                    csharpType = "long";
                    break;
                case "binary":
                case "image":
                case "varbinary":
                    csharpType = "byte[]";
                    break;
                case "bit":
                    csharpType = "bool";
                    break;
                case "datetime":
                case "smalldatetime":
                case "timestamp":
                    csharpType = "DateTime";
                    break;
                case "real":
                case "float":
                    csharpType = "float";
                    break;
                case "money":
                case "smallmoney":
                case "decimal":
                case "numeric":
                    csharpType = "decimal";
                    break;
                case "smallint":
                    csharpType = "short";
                    break;
                case "tinyint":
                    csharpType = "byte";
                    break;
                case "uniqueidentifier":
                    csharpType = "Guid";
                    break;
                case "variant":
                    csharpType = "object";
                    break;
                default:
                    csharpType = "string";
                    break;
            }

            return !new[] { "string", "object" }.Contains(csharpType) && isNullable ? csharpType + "?" : csharpType;
        }

        private string GetNotMappedProprety(string text)
        {
            int idx = text.IndexOf("[NotMapped]", 0);
            if (idx < 0) return string.Empty;

            int propertyBeginIdx = text.LastIndexOf('}', idx) + 1;
            string surplus = text.Substring(propertyBeginIdx);
            string property = string.Empty;
            idx = 0;
            int bracketCnt = 0;
            while (true)
            {
                int firstIdx = surplus.IndexOf('{', idx);//第一个括号下标;
                if (idx < 0) break;
                ++bracketCnt;//记录第一个括号
                ++firstIdx;
                if (surplus.IndexOf('{', firstIdx) > -1)
                {
                    idx = surplus.IndexOf('{', firstIdx);
                    string str = surplus.Substring(firstIdx, idx - firstIdx);//第二个括号下标
                    bracketCnt -= str.Length - str.Replace("}", "").Length;//一共有多少右括号
                }
                else
                {
                    idx = firstIdx;
                    do
                    {
                        idx = surplus.IndexOf('}', idx) + 1;
                    } while (--bracketCnt > 0);
                }
                if (bracketCnt < 1)
                {
                    property = surplus.Substring(0, surplus.LastIndexOf('}', idx) + 1).Trim('\r', '\n');
                    break;
                }//判断括号是否匹配
            }

            if (string.IsNullOrWhiteSpace(property)) return string.Empty;
            surplus = surplus.Substring(property.Length);
            // 从第一个{到第二个{中是否有}如果有则不添加{
            StringBuilder sbdr = new StringBuilder((int)2e2);
            sbdr.AppendLine(property);
            string nextPreperty = GetNotMappedProprety(surplus);
            if (!string.IsNullOrWhiteSpace(nextPreperty)) sbdr.AppendLine(nextPreperty);
            return sbdr.ToString();
        }

        private string ToCamelString(string tableName)
            => string.Join("", tableName.Split(new[] { '_' }, StringSplitOptions.RemoveEmptyEntries).Select(a => a.Substring(0, 1).ToUpper() + a.Substring(1)));

        private string GetCSharpComment(string comment, int level)
        {
            var tab = string.Concat(Enumerable.Repeat("    ", level));
            return @$"{tab}/// <summary>\r\n {tab} {comment}\r\n{tab}/// </summary>";
        }

        /// <summary>
        ///     INI读取
        /// </summary>
        /// <param name="section">要读取的段落名</param>
        /// <param name="key">要读取的键</param>
        /// <param name="defVal">读取异常的情况下的缺省值</param>
        /// <param name="retVal">key所对应的值，如果该key不存在则返回空值</param>
        /// <param name="size">值允许的大小</param>
        /// <param name="filePath">INI文件的完整路径和文件名</param>
        /// <returns></returns>
        [DllImport("kernel32")]
        private static extern int GetPrivateProfileString(string section, string key, string defVal, StringBuilder retVal, int size, string filePath);

        /// <summary>
        ///     INI写
        /// </summary>
        /// <param name="section">要写入的段落名</param>
        /// <param name="key">要写入的键，如果该key存在则覆盖写入</param>
        /// <param name="val">key所对应的值</param>
        /// <param name="filePath">INI文件的完整路径和文件名</param>
        /// <returns></returns>
        [DllImport("kernel32")]
        private static extern long WritePrivateProfileString(string section, string key, string val, string filePath);
    }
}